package org.zero.db;

import lombok.SneakyThrows;
import org.zero.exception.UtilException;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * @author Zero
 */
public class DbUtil {

    private DbUtil() {
    }

    /**
     * 添加数据到数据库，并返回插入主键值（id），默认不关闭连接
     *
     * @param connection 数据库连接
     * @param sql        待执行的保存语句
     * @param clazz      封装返回的主键类型
     * @param args       参数
     * @param <T>        主键泛型
     * @return 插入本条数据所生成的主键
     * @throws Exception SQL异常
     */
    public static <T extends Number> T insert(Connection connection,
                                              String sql,
                                              Class<T> clazz,
                                              Object... args) {
        return insert(connection, false, sql, clazz, args);
    }

    /**
     * 添加数据到数据库，并返回插入主键值（id）
     *
     * @param connection
     * @param closed
     * @param sql
     * @param clazz
     * @param args
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T extends Number> T insert(Connection connection,
                                              boolean closed,
                                              String sql,
                                              Class<T> clazz,
                                              Object... args) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        T result = null;

        try {
            preparedStatement = getPreparedStatement(connection, sql, args);
            preparedStatement.executeUpdate();
            resultSet = preparedStatement.getGeneratedKeys();
            // 封装主键
            if (resultSet.next()) {
                result = resultSet.getObject(1, clazz);
            }
        } catch (SQLException e) {
            throw new UtilException(e);
        } finally {
            // 关闭相关资源
            closeResultSet(resultSet);
            closePreparedStatement(preparedStatement);
            if (closed) {
                closeConnection(connection);
            }
        }
        return result;
    }

    /**
     * 修改数据库（包括增删改操作），默认不关闭连接
     *
     * @param connection 数据库连接
     * @param sql        SQL语句
     * @param args       参数
     * @return 返回影响的行数（-1：错误返回）
     */
    public static int revise(Connection connection, String sql, Object... args) {
        return revise(connection, false, sql, args);
    }

    /**
     * 修改数据库（包括增删改操作）
     *
     * @param connection 数据库连接
     * @param closed     是否关闭连接
     * @param sql        SQL语句
     * @param args       参数
     * @return 返回影响的行数（-1：错误返回）
     */
    public static int revise(Connection connection, boolean closed, String sql, Object... args) {
        PreparedStatement preparedStatement = null;
        int result = -1;

        try {
            preparedStatement = getPreparedStatement(connection, sql, args);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new UtilException(e);
        } finally {
            // 关闭相关资源
            closePreparedStatement(preparedStatement);
            if (closed) {
                closeConnection(connection);
            }
        }
        return result;
    }

    /**
     * 查询一行多列，并把结果集封装成 Map<String, Object>，默认不关闭连接
     *
     * @param connection 数据库连接
     * @param sql        SQL语句
     * @param args       不定长形参，传入需要设置占位的数据
     * @return Map<String, Object>
     */
    public static Map<String, Object> getMap(Connection connection, String sql, Object... args) {
        return getMap(connection, false, sql, args);
    }

    /**
     * 查询一行多列，并把结果集封装成 Map<String, Object>
     *
     * @param connection 数据库连接
     * @param closed     是否关闭连接
     * @param sql        SQL语句
     * @param args       不定长形参，传入需要设置占位的数据
     * @return Map<String, Object>
     */
    public static Map<String, Object> getMap(Connection connection, boolean closed, String sql, Object... args) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Map<String, Object> map = new LinkedHashMap<>();

        try {
            preparedStatement = getPreparedStatement(connection, sql, args);
            resultSet = preparedStatement.executeQuery();
            // 获得结果集结构信息——元数据
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            // 获得列数
            int columnCount = resultSetMetaData.getColumnCount();
            // 封装的 Map 对象
            map = new LinkedHashMap<>(columnCount);
            // 封装数据信息
            if (resultSet.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    map.put(resultSetMetaData.getColumnName(i), resultSet.getObject(i));
                }
            }
        } catch (Exception e) {
            throw new UtilException(e);
        } finally {
            // 关闭相关资源
            closeResultSet(resultSet);
            closePreparedStatement(preparedStatement);
            if (closed) {
                closeConnection(connection);
            }
        }
        return map;
    }

    /**
     * 查询多行多列，并把结果集封装成 List<Map<String, Object>>，默认不关闭连接
     *
     * @param connection 数据库连接
     * @param sql        SQL语句
     * @param args       不定长形参，传入需要设置占位的数据
     * @return List<Map < String, Object>>
     */
    public static List<Map<String, Object>> listMaps(Connection connection, String sql, Object... args) {
        return listMaps(connection, false, sql, args);
    }

    /**
     * 查询多行多列，并把结果集封装成 List<Map<String, Object>>
     *
     * @param connection 数据库连接
     * @param closed     是否关闭连接
     * @param sql        SQL语句
     * @param args       不定长形参，传入需要设置占位的数据
     * @return List<Map < String, Object>>
     */
    public static List<Map<String, Object>> listMaps(Connection connection, boolean closed, String sql,
                                                     Object... args) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Map<String, Object>> list = new ArrayList<>();

        try {
            preparedStatement = getPreparedStatement(connection, sql, args);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int columnCount = resultSetMetaData.getColumnCount();
                Map<String, Object> rowData = new LinkedHashMap<>(columnCount);
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(resultSetMetaData.getColumnName(i), resultSet.getObject(i));
                }
                list.add(rowData);
            }
        } catch (SQLException e) {
            throw new UtilException(e);
        } finally {
            closeResultSet(resultSet);
            closePreparedStatement(preparedStatement);
            if (closed) {
                closeConnection(connection);
            }
        }
        return list;
    }

    /**
     * 查询一行多列，并把结果集封装成实体类，默认不关闭连接
     *
     * @param <T>        实体泛型
     * @param connection 数据库连接
     * @param sql        SQL语句
     * @param clazz      实体类型
     * @param args       不定长形参，传入需要设置占位的数据
     * @return 封装的实体类对象
     */
    public static <T> T getBean(Connection connection, String sql, Class<T> clazz, Object... args) {
        return getBean(connection, false, sql, clazz, args);
    }

    /**
     * 查询一行多列，并把结果集封装成实体类
     *
     * @param <T>        实体类型
     * @param connection 数据库连接
     * @param closed     是否关闭连接
     * @param sql        SQL语句
     * @param clazz      实体类
     * @param args       不定长形参，传入需要设置占位的数据
     * @return 封装的实体类对象
     */
    public static <T> T getBean(Connection connection, boolean closed, String sql, Class<T> clazz,
                                Object... args) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        T t = null;

        try {
            preparedStatement = getPreparedStatement(connection, sql, args);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                t = setToBean(resultSet, clazz);
            }
        } catch (Exception e) {
            throw new UtilException(e);
        } finally {
            closeResultSet(resultSet);
            closePreparedStatement(preparedStatement);
            if (closed) {
                closeConnection(connection);
            }
        }
        return t;
    }

    /**
     * 查询多行多列，并把结果集封装成实体类集合，默认不关闭连接
     *
     * @param <T>        实体泛型
     * @param connection 数据库连接
     * @param sql        SQL语句
     * @param clazz      实体类型
     * @param args       不定长形参，传入需要设置占位的数据
     * @return 封装的实体类对象集合
     */
    public static <T> List<T> listBeans(Connection connection, String sql, Class<T> clazz, Object... args) {
        return listBeans(connection, false, sql, clazz, args);
    }

    /**
     * 查询多行多列，并把结果集封装成实体类集合
     *
     * @param <T>        实体类型
     * @param connection 数据库连接
     * @param closed     是否关闭连接
     * @param sql        SQL语句
     * @param clazz      实体类
     * @param args       不定长形参，传入需要设置占位的数据
     * @return 封装的实体类对象集合
     */
    public static <T> List<T> listBeans(Connection connection,
                                        boolean closed,
                                        String sql,
                                        Class<T> clazz,
                                        Object... args) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<T> list = new ArrayList<>();

        try {
            preparedStatement = getPreparedStatement(connection, sql, args);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                T t = setToBean(resultSet, clazz);
                list.add(t);
            }
        } catch (Exception e) {
            throw new UtilException(e);
        } finally {
            closeResultSet(resultSet);
            closePreparedStatement(preparedStatement);
            if (closed) {
                closeConnection(connection);
            }
        }
        return list;
    }

    /**
     * 查询一行一列，封装成指定类型，默认不关闭数据库连接
     *
     * @param <T>
     * @param connection
     * @param sql
     * @param clazz
     * @param args
     * @return
     * @throws Exception
     */
    public static <T> T getObject(Connection connection,
                                  String sql,
                                  Class<T> clazz,
                                  Object... args) {
        return getObject(connection, false, sql, clazz, args);
    }

    /**
     * 查询一行一列，封装成指定类型
     *
     * @param <T>
     * @param connection
     * @param closed
     * @param sql
     * @param clazz
     * @param args
     * @return
     * @throws Exception
     */
    public static <T> T getObject(Connection connection,
                                  boolean closed,
                                  String sql,
                                  Class<T> clazz,
                                  Object... args) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        T t = null;

        try {
            preparedStatement = getPreparedStatement(connection, sql, args);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                t = resultSet.getObject(1, clazz);
            }
        } catch (Exception e) {
            throw new UtilException(e);
        } finally {
            closeResultSet(resultSet);
            closePreparedStatement(preparedStatement);
            if (closed) {
                closeConnection(connection);
            }
        }
        return t;
    }

    /**
     * 查询多行一列，封装成指定类型集合，默认不关闭数据库连接
     *
     * @param connection
     * @param sql
     * @param clazz
     * @param args
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> listObjects(Connection connection,
                                          String sql,
                                          Class<T> clazz,
                                          Object... args) {
        return listObjects(connection, false, sql, clazz, args);
    }

    /**
     * 查询多行一列，封装成指定类型集合
     *
     * @param connection
     * @param closed
     * @param sql
     * @param clazz
     * @param args
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> listObjects(Connection connection,
                                          boolean closed,
                                          String sql,
                                          Class<T> clazz,
                                          Object... args) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<T> list = new ArrayList<>();

        try {
            preparedStatement = getPreparedStatement(connection, sql, args);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                T t = resultSet.getObject(1, clazz);
                list.add(t);
            }
        } catch (Exception e) {
            throw new UtilException(e);
        } finally {
            closeResultSet(resultSet);
            closePreparedStatement(preparedStatement);
            if (closed) {
                closeConnection(connection);
            }
        }
        return list;
    }

    /**
     * 获取 PreparedStatement 对象
     *
     * @param connection 数据库连接
     * @param sql        SQL语句
     * @param args       不定长形参，传入需要设置占位的数据
     * @return PreparedStatement 对象
     */
    private static PreparedStatement getPreparedStatement(Connection connection, String sql, Object... args) {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            // 设置占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
        } catch (SQLException e) {
            throw new UtilException(e);
        }
        return preparedStatement;
    }

    /**
     * 关闭 PreparedStatement
     *
     * @param preparedStatement PreparedStatement 对象
     */
    @SneakyThrows
    private static void closePreparedStatement(PreparedStatement preparedStatement) {
        if (preparedStatement != null) {
            preparedStatement.close();
        }
    }

    /**
     * 关闭结果集
     *
     * @param resultSet 结果集
     */
    @SneakyThrows
    private static void closeResultSet(ResultSet resultSet) {
        if (resultSet != null) {
            resultSet.close();
        }
    }

    /**
     * 关闭数据库连接
     *
     * @param connection 数据库连接
     */
    @SneakyThrows
    public static void closeConnection(Connection connection) {
        if (connection != null) {
            connection.close();
        }
    }

    /**
     * 封装结果集到实体类对象
     *
     * @param resultSet 结果集
     * @param clazz     实体类字节码
     * @param <T>       实体类泛型
     * @return 实体类对象
     */
    @SneakyThrows
    private static <T> T setToBean(ResultSet resultSet, Class<T> clazz) {
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        T t = clazz.getDeclaredConstructor().newInstance();

        for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
            // 获取列名，将标准数据库列名转化为标准Java变量名
            String columnName = resultSetMetaData.getColumnName(i);
            String[] splitNames = columnName.split("_");
            // 判断是否为 bool 字段
            StringBuilder sb;
            int j = 0;
            if (Objects.equals(splitNames[j], "is")) {
                sb = new StringBuilder(splitNames[++j]);
            } else {
                sb = new StringBuilder(splitNames[j]);
            }
            for (j++; j < splitNames.length; j++) {
                sb.append(rename(splitNames[j]));
            }
            columnName = sb.toString();

            // 如果变量名与列名不符，跳过此次循环
            Field field;
            try {
                field = clazz.getDeclaredField(columnName);
            } catch (NoSuchFieldException ignored) {
                continue;
            }

            // 获取该列名对应的 set 方法并调用
            Method method = clazz.getDeclaredMethod("set" + rename(columnName), field.getType());
            if (resultSet.getObject(i) != null) {
                method.invoke(t, resultSet.getObject(i, field.getType()));
            }
        }

        return t;
    }

    /**
     * 将字符串的首字母转大写
     *
     * @param str 需要转换的字符串
     * @return 转换后的字串
     */
    private static String rename(String str) {
        char[] chars = str.toCharArray();
        // 首字母的 ascii 码前移，即小写转大写
        chars[0] -= 32;
        return String.valueOf(chars);
    }
}
